package com.sjy.shopmall.dao;

import com.sjy.shopmall.pojo.OrderItem;
import com.sjy.shopmall.vo.CommentDTO;
import com.sjy.shopmall.vo.OrderItemDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.BaseMapper;

import java.math.BigDecimal;
import java.util.List;

@Mapper
public interface OrderItemMapper extends BaseMapper<OrderItem> {

    @Select("SELECT\n" +
            "\toi.order_id,\n" +
            "\toi.rate,\n" +
            "\toi.comment_date,\n" +
            "\toi.buyer_comment,\n" +
            "\toi.good_id,\n" +
            "\toi.good_price,\n" +
            "\toi.good_count,\n" +
            "\toi.id,\n" +
            "\tg.`name` good_name,\n" +
            "\tg.pic AS pic \n" +
            "FROM\n" +
            "\torder_item oi\n" +
            "\tLEFT JOIN goods g ON g.id = oi.good_id \n" +
            "WHERE\n" +
            "\toi.order_id = #{arg0};")
    List<OrderItemDTO> selectByOrder(Long id);

    @Select("SELECT AVG(oi.rate) FROM order_item oi WHERE oi.good_id=#{arg0} AND oi.comment_date is NOT NULL")
    BigDecimal selectAvgRate(Long goodId);

    @Select("SELECT\n" +
            "\toi.rate,\n" +
            "\toi.buyer_comment comments,\n" +
            "\tu.user_name,\n" +
            "\to.create_date order_date,\n" +
            "\toi.comment_date \n" +
            "FROM\n" +
            "\torder_item oi\n" +
            "\tLEFT JOIN sys_order o ON o.id = oi.order_id\n" +
            "\tLEFT JOIN `user` u ON u.id = o.user_id \n" +
            "WHERE\n" +
            "\toi.good_id = #{arg0} \n" +
            "\tAND oi.comment_date IS NOT NULL order by comment_date desc limit 20;")
    List<CommentDTO.CommentInfo> selectComments(Long goodId);
}
